
if exists (select * from sys.objects where name='UP_PCategory_GetPHotKeyWords' and type='P')
drop proc UP_PCategory_GetPHotKeyWords
go

create Proc UP_PCategory_GetPHotKeyWords --UP_PCategory_GetPHotKeyWords 1,2
(
	@LanguageID int,
	@ParentID int
)
as
	select * from (
	select a.CategoryID,a.ParentID,a.SortOrder,a.LanguageID,b.CategoryName from 
	PHotKeyWords as A inner join PCategory as B on a.CategoryID=b.CategoryID 
	union all 
	select CategoryID,ParentID,1 SortOrder,@LanguageID LanguageID,CategoryName from (
	select rank() over(partition by parentid order by CategoryID) rowID,* from PCategory where parentid in (
	select CategoryID from PCategory where LanguageID=@LanguageID and parentid=0 and isLeaf=0
	) and LanguageID=@LanguageID 
	)tmp where rowid=1 and parentid not in (select parentid from PHotKeyWords) 
	)tt where parentid=@ParentID 